package dao;

import java.sql.*;
import java.util.Vector;

import org.eclipse.jdt.annotation.NonNull;

import bean.Category;

public class CategoryDAO {
    private Connection conn;
    
    public CategoryDAO(@NonNull Connection conn) {
		this.conn = conn;
	}

	public boolean insert(@NonNull String name) throws SQLException {
        if (name.length() < 1) {
            return false;
        }
        PreparedStatement ps = conn.prepareStatement("INSERT INTO CATEGORIES VALUES(?)");
        ps.setString(1, name);
        boolean state = ps.executeUpdate() == 1;
        ps.close();
        return state;
    }

    public Category selectById(int id) throws SQLException {
        Category category = null;
        if (id < 1) {
            return category;
        }
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM CATEGORIES WHERE CATEGORY_ID=?");
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            String name = rs.getString("CATEGORY_NAME");
            category = new Category(id, name);
        }
        rs.close();
        ps.close();
        return category;
    }

    public Category selectByName(@NonNull String name) throws SQLException {
        Category category = null;
        if (name.length() < 1) {
            return category;
        }
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM CATEGORIES WHERE CATEGORY_NAME=?");
        ps.setString(1, name);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            int id = rs.getInt("CATEGORY_ID");
            category = new Category(id, name);
        }
        rs.close();
        ps.close();
        return category;
    }

    public Vector<Category> selectAll() throws SQLException {
        Vector<Category> categories = new Vector<>();
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM CATEGORIES ORDER BY CATEGORY_ID");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            int id = rs.getInt("CATEGORY_ID");
            String name = rs.getString("CATEGORY_NAME");
            Category category = new Category(id, name);
            categories.addElement(category);
        }
        rs.close();
        ps.close();
        return categories;
    }

    public boolean update(int id, @NonNull String name) throws SQLException {
        if (id < 1 || name.length() < 1) {
            return false;
        }
        PreparedStatement ps = conn.prepareStatement("UPDATE CATEGORIES SET CATEGORY_NAME=? WHERE CATEGORY_ID=?");
        ps.setString(1, name);
        ps.setInt(2, id);
        boolean state = ps.executeUpdate() == 1;
        ps.close();
        conn.close();
        return state;
    }

    public boolean delete(int id) throws SQLException {
        if (id < 1) {
            return false;
        }
        PreparedStatement ps = conn.prepareStatement("DELETE FROM CATEGORIES WHERE CATEGORY_ID=?");
        ps.setInt(1, id);
        boolean state = ps.executeUpdate() == 1;
        ps.close();
        return state;
    }
}
